CREATE OR REPLACE FUNCTION encryptPassword (password IN NVARCHAR2) RETURN RAW
IS
    encryptedPassword RAW(20);
BEGIN
    SELECT DBMS_CRYPTO.HASH(utl_raw.cast_to_raw(password), DBMS_CRYPTO.HASH_SH1)
        INTO encryptedPassword
        FROM  DUAL;
    return encryptedPassword;
END;
/

CREATE TABLE USERS(
    userID NUMBER CONSTRAINT PK_USERS PRIMARY KEY,
    username NVARCHAR2(20) CONSTRAINT UK1_USERS UNIQUE NOT NULL,
    password RAW(20) NOT NULL,
    name NVARCHAR2(20) NOT NULL,
    surname NVARCHAR2(50) NOT NULL,
    birthDate DATE,
    sex CHAR(1 CHAR) NOT NULL,
    email VARCHAR2(60 CHAR) CONSTRAINT UK2_USERS UNIQUE NOT NULL,
    picture BLOB,
    correctAnswers NUMBER DEFAULT 0,
    wrongAnswers NUMBER DEFAULT 0,
    type VARCHAR2(13 CHAR) DEFAULT 'PENDING' NOT NULL,
    CONSTRAINT CK1_USERS CHECK (sex IN ('M', 'F')),
    CONSTRAINT CK2_USERS CHECK (REGEXP_LIKE(email, '[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}')),
    CONSTRAINT CK3_USERS CHECK (type IN ('ADMINISTRATOR', 'PENDING', 'REGULAR'))
);

CREATE SEQUENCE USERS_ID_SEQUENCE;

CREATE OR REPLACE TRIGGER USERS_ID_TRIGGER
    BEFORE INSERT ON USERS
    FOR EACH ROW
    BEGIN
    SELECT USERS_ID_SEQUENCE.nextval INTO:new.userID FROM dual;
END;
/

CREATE TABLE ADMINISTRATORS(
    adminID NUMBER CONSTRAINT PK_ADMINISTRATORS PRIMARY KEY CONSTRAINT FK_ADMINISTRATORS REFERENCES USERS
);

CREATE TABLE PENDING_USERS(
    pendingID NUMBER CONSTRAINT PK_PENDING_USERS PRIMARY KEY CONSTRAINT FK_PENDING_USERS REFERENCES USERS ON DELETE CASCADE
);

CREATE TABLE REGULAR_USERS(
    regularID NUMBER CONSTRAINT PK_REGULAR_USERS PRIMARY KEY CONSTRAINT FK_REGULAR_USERS REFERENCES USERS ON DELETE CASCADE
);

CREATE OR REPLACE TRIGGER PENDING_USERS_TRIGGER
    AFTER INSERT ON USERS
    FOR EACH ROW
    BEGIN
    IF(:new.type = 'PENDING') THEN
    INSERT INTO PENDING_USERS(pendingID) VALUES (:new.userID);
END IF;
END;
/
    
CREATE OR REPLACE TRIGGER CHANGING_USERS_TRIGGER
    AFTER UPDATE ON USERS FOR EACH ROW
    BEGIN
    IF(:old.type = 'PENDING') THEN
    DELETE FROM PENDING_USERS WHERE pendingID = :old.userID;
ELSE
    IF(:old.type = 'REGULAR') THEN
    DELETE FROM REGULAR_USERS WHERE regularID = :old.userID;
ELSE
    IF(:old.type = 'ADMINISTRATOR') THEN
    DELETE FROM ADMINISTRATORS WHERE adminID = :old.userID;
END IF;
END IF;
END IF;
IF(:new.type = 'PENDING') THEN
    INSERT INTO PENDING_USERS(pendingID) VALUES(:new.userID);
ELSE
    IF(:new.type = 'REGULAR') THEN
    INSERT INTO REGULAR_USERS(regularID) VALUES(:new.userID);
ELSE
    IF(:new.type = 'ADMINISTRATOR') THEN
    INSERT INTO ADMINISTRATORS(adminID) VALUES(:new.userID);
END IF;
END IF;
END IF;
END;
/
        

CREATE TABLE GROUPS(
    groupID NUMBER CONSTRAINT PK_GROUPS PRIMARY KEY,
    name NVARCHAR2(50) CONSTRAINT UK_GROUPS UNIQUE NOT NULL
);

CREATE SEQUENCE GROUPS_ID_SEQUENCE;

CREATE OR REPLACE TRIGGER GROUPS_ID_TRIGGER
    BEFORE INSERT ON GROUPS
    FOR EACH ROW
    BEGIN
    SELECT GROUPS_ID_SEQUENCE.nextval INTO :new.groupID FROM dual;
END;
/

CREATE TABLE MEMBERS(
    userID NUMBER CONSTRAINT FK1_MEMBERS REFERENCES USERS ON DELETE CASCADE,
    groupID NUMBER CONSTRAINT FK2_MEMBERS REFERENCES GROUPS ON DELETE CASCADE,
    CONSTRAINT PK_MEMBERS PRIMARY KEY (userID, groupID)
);

CREATE TABLE TEXTS(
    textNumber NUMBER CONSTRAINT PK_TEXTS PRIMARY KEY,
    title NVARCHAR2(100) CONSTRAINT UK_TEXTS UNIQUE NOT NULL,
    dataFile BLOB NOT NULL,
    secondsToRead NUMBER NOT NULL,
    secondsToAnswer NUMBER NOT NULL
);

CREATE SEQUENCE TEXTS_ID_SEQUENCE;

CREATE OR REPLACE TRIGGER TEXTS_ID_TRIGGER
    BEFORE INSERT ON TEXTS
    FOR EACH ROW
    BEGIN
    SELECT TEXTS_ID_SEQUENCE.nextval INTO :new.textNumber FROM dual;
END;
/

CREATE TABLE PENDING_TEXTS(
    userID NUMBER CONSTRAINT FK1_PENDING_TEXTS REFERENCES USERS ON DELETE CASCADE,
    textNumber NUMBER CONSTRAINT FK2_PENDING_TEXTS REFERENCES TEXTS ON DELETE CASCADE,
    CONSTRAINT PK_PENDING_TEXTS PRIMARY KEY (userID, textNumber)
);

CREATE TABLE RESOLVED_TEXTS(
    userID NUMBER CONSTRAINT FK1_REALIZADOS REFERENCES USERS ON DELETE CASCADE,
    textNumber NUMBER CONSTRAINT FK2_REALIZADOS REFERENCES TEXTS ON DELETE CASCADE,
    mark NUMBER NOT NULL,
    dateOfExam TIMESTAMP WITH LOCAL TIME ZONE,
    CONSTRAINT PK_RESOLVED_TEXTS PRIMARY KEY (userID, textNumber),
    CONSTRAINT CK1_RESOLVED_TEXTS CHECK (mark BETWEEN 0 AND 10)
);

CREATE OR REPLACE TRIGGER RESOLVED_DATE_TRIGGER
BEFORE INSERT ON RESOLVED_TEXTS
FOR EACH ROW
BEGIN
    SELECT CURRENT_DATE INTO :new.dateOfExam FROM DUAL;
END;
/ 

CREATE TABLE ASSIGNED_TEXTS(
    groupID NUMBER CONSTRAINT FK1_ASSIGNED_TEXTS REFERENCES GROUPS ON DELETE CASCADE,
    textNumber NUMBER CONSTRAINT FK2_ASSIGNED_TEXTS REFERENCES TEXTS ON DELETE CASCADE
);

CREATE TABLE QUESTIONS(
    textNumber NUMBER CONSTRAINT FK_QUESTIONS REFERENCES TEXTS ON DELETE CASCADE,
    questionNumber NUMBER,
    question NVARCHAR2(500) NOT NULL,
    correctAnswer NVARCHAR2(500) NOT NULL,
    incorrectAnswer1 NVARCHAR2(500) NOT NULL,
    incorrectAnswer2 NVARCHAR2(500) NOT NULL,
    incorrectAnswer3 NVARCHAR2(500) NOT NULL,
    CONSTRAINT PK_QUESTIONS PRIMARY KEY (textNumber, questionNumber)
);

CREATE OR REPLACE TRIGGER QUESTIONS_NUM_TRIGGER
        BEFORE INSERT ON QUESTIONS
        FOR EACH ROW
        DECLARE
        maxnum NUMBER;
        BEGIN
        SELECT max(questionNumber) INTO maxnum FROM QUESTIONS
        WHERE textNumber = :new.textNumber;
        IF (maxnum IS NULL) THEN
        SELECT 1 INTO:new.questionNumber FROM dual;
        ELSE
        SELECT maxnum+1 INTO:new.questionNumber FROM dual;
        END IF;
END;
/

CREATE TABLE ANSWERS(
    userID NUMBER,
    textNumber NUMBER,
    questionNumber NUMBER,
    selectedAnswer NUMBER NOT NULL,
    CONSTRAINT FK_ANSWERS FOREIGN KEY(userID, textNumber) REFERENCES RESOLVED_TEXTS(userID, textNumber) ON DELETE CASCADE,
    CONSTRAINT PK_ANSWERS PRIMARY KEY(userID, textNumber, questionNumber),
    CONSTRAINT CK1_ANSWERS CHECK (selectedAnswer BETWEEN 0 AND 4)
);

CREATE OR REPLACE TRIGGER DOES_QUESTION_EXIST
    BEFORE INSERT ON ANSWERS
    FOR EACH ROW
    DECLARE
    question NUMBER;
BEGIN
    SELECT questionNumber INTO question FROM QUESTIONS
    WHERE (textNumber = :new.textNumber) AND (questionNumber= :new.questionNumber);
IF (question IS NULL)
    THEN raise_application_error(20001, 'That question does not exist');
END IF;
END;
/